﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using KPIS.GERP.GHRMS.DAL;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IBLL; 
using KPIS.DBM;

using KPIS.GERP.GHRMS.DALFactory;

namespace KPIS.GERP.GHRMS.BLL
{
    public class SalaryPromotionBaseConfigBLL
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public void Insert(int prm_seq,int sal_grp_seq, string sal_level_name, Double sal_base, Double sal_min,Double  sal_max,Double sal_extra_base,Double sal_extra_min,Double sal_extra_max, int created_by)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(12);
                dbManager.AddParameters(0, "@prm_seq", prm_seq);
                dbManager.AddParameters(1, "@sal_base", sal_base);
                dbManager.AddParameters(2, "@sal_level_name", sal_level_name);
                dbManager.AddParameters(3, "@sal_min", sal_min);
                dbManager.AddParameters(4, "@sal_max", sal_max);
                dbManager.AddParameters(5, "@sal_extra_base", sal_extra_base);
                dbManager.AddParameters(6, "@sal_extra_min", sal_extra_min);
                dbManager.AddParameters(7, "@sal_extra_max", sal_extra_max);
                dbManager.AddParameters(8, "@sal_grp_seq", sal_grp_seq);
                dbManager.AddParameters(9, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(10, "@created_by", created_by);
                dbManager.AddParameters(11, "@created_when", DateTime.Today);

                string strSQL;

                strSQL = " insert into hrm_prm_promotion_bases(prm_seq,sal_base,sal_min,sal_max,"
                    + " sal_extra_base,sal_extra_min,sal_extra_max, "
                    + " record_status,created_by,created_when,sal_grp_seq,sal_level_name) "
                    + " values(@prm_seq,@sal_base,@sal_min,@sal_max, "
                    + " @sal_extra_base,@sal_extra_min,@sal_extra_max, "
                    + " @record_status,@created_by,@created_when,@sal_grp_seq,@sal_level_name)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);                

            }
            catch (Exception ex)
            {
                throw new Exception("{Insert_hrm_es_move_requests}" + ex.Message);
            }
            finally
            {
                dbManager.Dispose();
            }

        }


        private int Ret_Pos_seq(int classID)
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = " select pos_type_seq "
                + " from hrm_mp_position_types "
                + " where class_seq =" + classID;

                DataSet ds = new DataSet();
                DataTable dt;

                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();

                if (dt.Rows.Count > 0)
                {
                    return int.Parse(dt.Rows[0]["pos_type_seq"].ToString());
                }
                else
                {
                    return -1;
                }
            }

            catch
            {
                return -1;
            }

            finally 

            {
                dbManager.Dispose(); 
            }
        }

        public DataTable BindText(int sal_grp_seq)
        {
            try
            
            {

                dbManager.Open();
                string sql;
               

                DataSet ds = new DataSet();
                DataTable dt;


                sql = " SELECT * from hrm_pr_salary_group_dtls "
                + "  where sal_grp_seq = " + sal_grp_seq;
               
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
                               
          
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                dbManager.Dispose(); 
            }
        }

        public void Update(int prm_base_seq, int sal_grp_seq, string sal_level_name, Double sal_base, Double sal_min, Double sal_max, Double sal_extra_base, Double sal_extra_min, Double sal_extra_max, int updated_by)

        {

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(11);
     
                dbManager.AddParameters(0, "@sal_base", sal_base);
                dbManager.AddParameters(1, "@sal_level_name", sal_level_name);
                dbManager.AddParameters(2, "@sal_min", sal_min);
                dbManager.AddParameters(3, "@sal_max", sal_max);
                dbManager.AddParameters(4, "@sal_extra_base", sal_extra_base);
                dbManager.AddParameters(5, "@sal_extra_min", sal_extra_min);
                dbManager.AddParameters(6, "@sal_extra_max", sal_extra_max);
                dbManager.AddParameters(7, "@sal_grp_seq", sal_grp_seq);
                dbManager.AddParameters(8, "@updated_by", updated_by);
                dbManager.AddParameters(9, "@updated_when", DateTime.Today);
                dbManager.AddParameters(10, "@prm_base_seq", prm_base_seq);
                string strSQL;

                strSQL = " update hrm_prm_promotion_bases set"
                    + " sal_base=@sal_base,sal_min=@sal_min,sal_max=@sal_max,sal_level_name=@sal_level_name,"
                    + " sal_extra_base=@sal_extra_base,sal_extra_min=@sal_extra_min,sal_extra_max=@sal_extra_max,"
                    + " updated_by=@updated_by,updated_when=@updated_when,sal_grp_seq=@sal_grp_seq "
                    + " where prm_base_seq=@prm_base_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

             }
            catch (Exception ex)
            {

            }
            finally
            {
                dbManager.Dispose();  
            }        
        
        }


        public void Delete(int prm_base_seq, int UpdatedBy)
        {
            try
            {

                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@updated_by", UpdatedBy);
                dbManager.AddParameters(2, "@updated_when", DateTime.Today  );
                dbManager.AddParameters(3, "@prm_base_seq", prm_base_seq);

                string sql;
                sql = " update hrm_prm_promotion_bases set "
                + " record_status=@record_status"
                + ", updated_by = @updated_by"
                  + " where prm_base_seq = @prm_base_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);
                
                //   dal.Delete(Sequence, UpdatedBy);
            }
            catch (Exception ex)
            {
                throw new Exception("");
            }
            finally
            {
                dbManager.Dispose();
            }
        }


        public DataTable  SelectComboClass(Nullable<int> PositionType)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@PositionType", PositionType);

                string strSQL;

                strSQL = "SELECT c.CLASS_NAME, g.SAL_GRP_SEQ"
                    + " FROM HRM_MP_CLASSES c"
                    + " LEFT JOIN HRM_MP_POSITION_TYPES pt ON c.POS_TYPE_SEQ = pt.POS_TYPE_SEQ"
                    + " LEFT JOIN HRM_PR_SALARY_GROUPS g ON c.CLASS_SEQ = g.CLASS_SEQ"
                            + " AND g.RECORD_STATUS = @record_status"
                    + " WHERE c.RECORD_STATUS = @record_status"
                    + (PositionType.HasValue ? " AND c.POS_TYPE_SEQ = @PositionType" : "");            

                DataSet ds = new DataSet();
                DataTable dt;

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dt = ds.Tables[0].Copy();

                return dt;
            }

            catch
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }


        }

        public DataTable select(int prm_seq)
        {
            try
            {
                string sql;

                sql = " SELECT pb.SAL_EXTRA_MAX, pb.SAL_EXTRA_MIN, pb.SAL_EXTRA_BASE, pb.SAL_LEVEL_NAME"
                    + ", pb.SAL_EXTRA, pb.SAL_MAX, pb.SAL_MIN, pb.SAL_BASE, pb.PRM_BASE_SEQ"
                    + ", pb.PRM_SEQ, pb.SAL_GRP_SEQ, c.CLASS_NAME, gd.SAL_TMP"
                    + ", gd.SAL_MIN AS D_SAL_MIN, gd.SAL_MAX AS D_SAL_MAX"
                    + ", CONCAT(gd.SAL_MIN, ' - ', gd.SAL_MAX) AS SAL_BETWEEN"
                    + ", c.POS_TYPE_SEQ, pt.POS_TYPE_NAME"
                    + " FROM HRM_PRM_PROMOTION_BASES pb"
                    + " INNER JOIN HRM_PR_SALARY_GROUPS g ON pb.SAL_GRP_SEQ = g.SAL_GRP_SEQ"
                    + " INNER JOIN HRM_PR_SALARY_GROUP_DTLS gd ON g.SAL_GRP_SEQ = gd.SAL_GRP_SEQ"
                    + " INNER JOIN HRM_MP_CLASSES c ON g.CLASS_SEQ = c.CLASS_SEQ"  
                    + " INNER JOIN HRM_MP_POSITION_TYPES pt ON c.POS_TYPE_SEQ = pt.POS_TYPE_SEQ"             
                    + " WHERE pb.PRM_SEQ = " + prm_seq
                    + " AND pb.RECORD_STATUS = 'U'";
           
                DataSet ds;
                DataTable dt;
                ds = new DataSet();
                dbManager.Open();
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                return null;
                // throw new Exception("");
            }
            finally
            {
                dbManager.Dispose();  
            }
        }
   
    }
}
